use master
if exists (select name from sys.databases where name =N'EmployeeTransferManagement')
drop database EmployeeTransferManagement
go
create database EmployeeTransferManagement
go
use EmployeeTransferManagement
--Table Location
go
create table Location(
	STT int identity(1,1),
	LocationID nvarchar(10) not null,
	LocationName nvarchar(50) not null,
	LocationAllowance money not null
)
alter table Location
add constraint pk_LocationID primary key (LocationID)
--	constraint pk_LocatioName primary key (LocationName)

--Table Department
go
create table Department(
	STT int identity(1,1),
	DepartmentID nvarchar(10) not null,
	DepartmentName nvarchar(50) not null,
	DepartmentAllowance money not null
)
alter table Department
add constraint pk_DepartmentID primary key (DepartmentID)

--Table Project
go
create table Project(
	STT int identity(1,1),
	ProjectID nvarchar(10) not null,
	ProjectName nvarchar(50) not null,
	ProjectAllowance money not null
)
alter table Project
add constraint pk_ProjectID primary key (ProjectID)

--Table Admin
create table Admin(
	STT int identity(1,1),
	Email nvarchar(50) not null,
	Password nvarchar(50) not null,
	FullName nvarchar(50) not null
	
)
alter table Admin
add constraint pk_Admin primary key (STT)

--Table Employee
go
create table Employee(
	STT int identity(1,1),
	EmployeeID nvarchar(10) not null,
	EmployeeName nvarchar(50) not null,
	Gender bit,
	DayOfBirth datetime not null,
	Phone nvarchar(15) not null,
	Address nvarchar(50) not null,
	Email nvarchar(50) not null,
	Password nvarchar(50),
	WorkExperience int not null,
	LocationID nvarchar(10) not null,
	DepartmentID nvarchar(10) not null,
	ProjectID nvarchar(10) not null,
)
alter table Employee
add constraint pk_Employee primary key (EmployeeID, LocationID, DepartmentID, ProjectID),
	constraint fk_Employee_Location foreign key (LocationID) references Location(LocationID),
	constraint fk_Employee_Department foreign key (DepartmentID) references Department(DepartmentID),
	constraint fk_Employee_Project foreign key (ProjectID) references Project(ProjectID)

--Table Tranfer
go
create table Transfer(
	STT int identity(1,1),
	TransferID nvarchar(10) not null,
	EmployeeID nvarchar(10) not null,
	FromProjectID nvarchar(10) not null,
	ToProjectID nvarchar(10) not null,
	FromDepartmentID nvarchar(10) not null,
	ToDepartmentID nvarchar(10) not null,
	FromLocationID nvarchar(10) not null,
	ToLocationID nvarchar(10) not null,
	JoinDate datetime null,
    EndDate datetime null,
	Status bit
)
alter table Transfer
add constraint pk_Transfer primary key (TransferID),
	constraint fk_Transfer_Employee foreign key (EmployeeID,FromLocationID,FromDepartmentID,FromProjectID) references Employee(EmployeeID,LocationID,DepartmentID,ProjectID),
	constraint fk_Transfer_Project foreign key (ToProjectID) references Project(ProjectID),
	constraint fk_Transfer_Department foreign key (ToDepartmentID) references Department(DepartmentID),
	constraint fk_Transfer_Location foreign key (ToLocationID) references Location(LocationID)

--------------------------------------------------------------------------------------------------------------------------------
--Insert Data
insert into Location values ('L01','Ha Noi',10000)
insert into Location values ('L02','TP Ho Chi Minh',200000)
insert into Location values ('L03','TP Hai Phong',50000)
insert into Location values ('L04','Thanh Hoa',30000)
insert into Location values ('L05','TP Can Tho',70000)

insert into Department values ('D01','Design',10000)
insert into Department values ('D02','Tester',30000)
insert into Department values ('D04','Manager',50000)
insert into Department values ('D03','Code',60000)


insert into Project values ('P01','Employee Tranfer Management', 100000) 
insert into Project values ('P02','ATM', 110000)
insert into Project values ('P03','Football Management', 200000)
--insert into Project values ('P04','Employee Tranfer Management', 105000)
insert into Project values ('P04','Hotel Management', 90000)
insert into Project values ('P05','Employee Management', 180000)
insert into Project values ('P06','Website Marketing', 300000)


insert into Admin values ('Catbui.nuce@gmail.com','123456','Nguyen Huu Tung')
insert into Admin values ('Vubinh55pm@gmail.com','719155','Vu Van Binh')

insert into Employee values ('E01','Nguyen Huu Thanh',1,'07/07/1992','0986771743','292 Hoang Mai','nuce@gmail.com','123789',10,'L01','D01','P01')
insert into Employee values ('E02','Vu Van An',1,'01/01/1992','0986674054','Hung Yen','BinhThuong@gmail.com','VuAn',7,'L01','D01','P01')
insert into Employee values ('E03','Ha Van Vuong',1,'07/09/1991','01236561991','292 Hoang Mai','ThinhVuong@gmail.com','Hienvuong',5,'L02','D01','P01')
insert into Employee values ('E04','Ha Thi Ngat',0,'07/01/1990','01236556991','292 Hoang Mai','ThinhNgat@gmail.com','Ngatvuong',9,'L03','D03','P02')
insert into Employee values ('E05','Vu Thi Men',0,'03/16/1992','01645805277','10 Hoang Mai','WilrdFloewr@gmail.com','tung',10,'L04','D04','P06')


insert into Transfer values ('T01','E01','P01','P02','D01','D01','L01','L02','07/07/2010','10/10/2011',0);
insert into Transfer values ('T02','E01','P01','P04','D01','D01','L01','L02','07/07/2010','10/10/2011',1);
insert into Transfer values ('T03','E02','P01','P05','D01','D02','L01','L01','07/07/2010','10/10/2011',0);
insert into Transfer values ('T04','E03','P01','P03','D01','D03','L02','L04','07/07/2010','10/10/2011',0);
insert into Transfer values ('T05','E05','P06','P05','D04','D04','L04','L03','07/07/2010','10/10/2011',0);
insert into Transfer values ('T06','E04','P02','P03','D03','D03','L03','L02','07/07/2010','10/10/2011',0);
insert into Transfer values ('T07','E02','P01','P01','D01','D03','L01','L02','07/07/2010','10/10/2011',0);

--------------------------------------------------------------------------------------------------------------------------------
--PROC TABLE EMPLOYEE
--Employee
go
create proc spGetEmployee
	@Email varchar(100)
as 
select * from Employee where Email=@Email

--Employee by Name
go
create proc spGetEmployeeName
	@EmployeeName nvarchar(50)
as
select * from Employee where EmployeeName = @EmployeeName

--Change Pass Employee
go
create proc spChangePasswordEmployee
	@oldPassword nvarchar(50),
	@newPassword nvarchar(50)
as
update Employee
set Password = @newPassword
where Password = @oldPassword

--Insert Employee
go  
create proc spInsertEmployee 
	@EmployeeID nvarchar(10),
	@EmployeeName nvarchar(50),
	@Gender bit,
	@DayOfBirth datetime,
	@Phone nvarchar(15),
	@Address nvarchar(50),
	@Email nvarchar(50),
	@Password nvarchar(50),
	@WorkExperience int,
	@LocationID nvarchar(10),
	@DepartmentID nvarchar(10),
	@ProjectID nvarchar(10)
as 
insert into Employee(EmployeeID, EmployeeName, Gender, DayOfBirth, Phone, Address, Email, Password, WorkExperience, LocationID, DepartmentID, ProjectID) 
values (@EmployeeID, @EmployeeName, @Gender, @DayOfBirth, @Phone, @Address, @Email, @Password, @WorkExperience, @LocationID, @DepartmentID, @ProjectID)

--Update Employee
go  
create proc spUpdateEmployee 
	@EmployeeID nvarchar(10),
	@EmployeeName nvarchar(50),
	@Gender bit,
	@DayOfBirth datetime,
	@Phone nvarchar(15),
	@Address nvarchar(50),
	@Email nvarchar(50),
	@Password nvarchar(50),
	@WorkExperience int,
	@LocationID nvarchar(10),
	@DepartmentID nvarchar(10),
	@ProjectID nvarchar(10)
as update Employee
set EmployeeName = @EmployeeName, Gender = @Gender, DayOfBirth = @DayOfBirth, Phone = @Phone, Address = @Address, Email = @Email, Password = @Password, LocationID = @LocationID, DepartmentID = @DepartmentID, ProjectID = @ProjectID
where EmployeeID = @EmployeeID   

--Delete Employee
go
create proc spDeleteEmployee
	@EmployeeID nvarchar(10)
as 
delete Employee where EmployeeID = @EmployeeID

--Get EmployeeID
go
create proc spGetEmployeeID
	@EmployeeName nvarchar(50)
as
select EmployeeID from EMployee where EmployeeName = @EmployeeName

--get Employee
go
create proc spEmployee
as
select * from Employee

--Search Employee By ID
go
create proc spSearchEmployeeByID
	@EmployeeID nvarchar(10)
as select * from Employee where EmployeeID=@EmployeeID

--Search Employee By Name
go
create proc spSearchEmployeeByName
	@EmployeeName nvarchar(50)
as select * from Employee where EmployeeName=@EmployeeName

--Search Employee By ProjectID
go
create proc spSearchEmployeeByProject
	@ProjectID nvarchar(10)
as select * from Employee where ProjectID = @ProjectID

--Search Employee By LocationID
go
create proc spSearchEmployeeByLocationID
	@LocationID nvarchar(10)
as select * from Employee where LocationID = @LocationID

--Search Employee By DepartmentID
go
create proc spSearchEmployeeByDepartmentID
	@DepartmentID nvarchar(10)
as select * from Employee where DepartmentID = @DepartmentID


--------------------------------------------------------------------------------------------------------------------------------
--PROC TABLE PROJECT
--Search Project by ID
go
create proc spSearchProjectByID
	@ProjectID nvarchar(10)
as select * from Project where ProjectID=@ProjectID

--Search Project by Name
go
create proc spSearchProjectByName
	@ProjectName nvarchar(50)
as select * from Project where ProjectName=@ProjectName

--get ProjectID
go
create proc spGetProjectID
	@ProjectName nvarchar(50)
as
select ProjectID from Project where ProjectName = @ProjectName

--Get ProjectName
go
create proc spGetProjectName
	@ProjectID nvarchar(10)
as
select ProjectName from Project where ProjectID = @ProjectID

--get Project
go
create proc spProject
as
select * from Project

--get Allowance Project
go
create proc spGetAllowanceProject
	@ProjectID nvarchar(10)
as
select ProjectAllowance from Project where ProjectID = @ProjectID

--------------------------------------------------------------------------------------------------------------------------------
--PROC TABLE LOCATION
--Search Location by ID
go
create proc spSearchLocationByID
	@LocationID nvarchar(10)
as select * from Location where LocationID=@LocationID

--Search Location by Name
go
create proc spSearchLocationByName
	@LocationName nvarchar(50)
as select * from Location where LocationName=@LocationName

--Get LocationID
go
create proc spGetLocationID
	@LocationName nvarchar(50)
as
select LocationID from Location where LocationName = @LocationName

--Get LocationName
go
create proc spGetLocationName
	@LocationID nvarchar(10)
as
select LocationName from Location where LocationID = @LocationID

--Get Location
go
create proc spLocation
as
select * from Location

--get Allowance Location
go
create proc spGetAllowanceLocation
	@LocationID nvarchar(10)
as
select LocationAllowance from Location where LocationID = @LocationID


--------------------------------------------------------------------------------------------------------------------------------
--PROC TABLE DEPARTMENT
--Search Department by ID
go
create proc spSearchDepartmentByID
	@DepartmentID nvarchar(10)
as select *from Department where DepartmentID=@DepartmentID

--Search Department by Name
go
create proc spSearchDepartmentByName
	@DepartmentName nvarchar(50)
as select * from Department where DepartmentName=@DepartmentName

--Get DepartmentID
go
create proc spGetDepartmentID
	@DepartmentName nvarchar(50)
as
select DepartmentID from Department where DepartmentName = @DepartmentName

--Get Department Name
go
create proc spGetDepartmentName
	@DepartmentID nvarchar(10)
as
select DepartmentName from Department where DepartmentID = @DepartmentID

--get Department
go
create proc spDepartment
as
select * from Department

--get Allowance Department
go
create proc spGetAllowanceDepartment
	@DepartmentID nvarchar(10)
as
select DepartmentAllowance from Department where DepartmentID = @DepartmentID


--------------------------------------------------------------------------------------------------------------------------------
--PROC TABLE ADMIN
--Admin
go
create proc spGetAdmin
	@Email varchar(100)
as
select * from Admin where Email=@Email

--Change Pass Admin
go
create proc spChangePasswordAdmin
	@oldPassword nvarchar(50),
	@newPassword nvarchar(50)
as 
update Admin
set Password = @newPassword
where Password = @oldPassword


--------------------------------------------------------------------------------------------------------------------------------
--PROC TABLE TRANSFER
--Insert Tranfer
go
create proc spInsertTranfer
	@TransferID nvarchar(10),
	@EmployeeID nvarchar(10),
	@FromProjectID nvarchar(10),
	@ToProjectID nvarchar(10),
	@FromDepartmentID nvarchar(10),
	@ToDepartmentID nvarchar(10),
	@FromLocationID nvarchar(10),
	@ToLocationID nvarchar(10),
	@JoinDate datetime ,
    @EndDate datetime,
	@Status bit
as
insert into Transfer(TransferID, EmployeeID, FromProjectID, ToProjectID, FromDepartmentID, ToDepartmentID, FromLocationID, ToLocationID, JoinDate, EndDate, Status)
values (@TransferID, @EmployeeID, @FromProjectID, @ToProjectID, @FromDepartmentID, @ToDepartmentID, @FromLocationID, @ToLocationID, @JoinDate, @EndDate, @Status)

--Update Transfer
go
create proc spUpDateTransfer
	@TransferID nvarchar(10),
	@EmployeeID nvarchar(10),
	@FromProjectID nvarchar(10),
	@ToProjectID nvarchar(10),
	@FromDepartmentID nvarchar(10),
	@ToDepartmentID nvarchar(10),
	@FromLocationID nvarchar(10),
	@ToLocationID nvarchar(10),
	@JoinDate datetime ,
    @EndDate datetime,
	@Status bit
as update Transfer
set EmployeeID = @EmployeeID, FromProjectID = @FromProjectID, ToProjectID = @ToProjectID, FromDepartmentID = @FromDepartmentID, ToDepartmentID = @ToDepartmentID, FromLocationID = @FromLocationID, ToLocationID = @ToLocationID, JoinDate = @JoinDate, EndDate = @EndDate, Status = @Status
where TransferID = @TransferID

--Delete Transfer
go
create proc spDeleteTransfer
	@TransferID nvarchar(10)
as
delete Transfer where TransferID = @TransferID

--Search Transfer By ID
go
create proc spSearchTransferID
	@TransferID nvarchar(10)
as
select * from Transfer where TransferID=@TransferID

--Search Transfer By EmployeeID
go
create proc spSearchTransferByEmployeeID
	@EmployeeID nvarchar(10)
as
select * from Transfer where EmployeeID = @EmployeeID

--Search Transfer By From LocationID
go
create proc spSearchTransferByFromLocationID
	@FromLocationID nvarchar(10)
as
select * from Transfer where FromLocationID = @FromLocationID

--Search Transfer By From DepartmentID
go
create proc spSearchTransferByFromDepartmentID
	@FromDepartmentID nvarchar(10)
as
select * from Transfer where FromDepartmentID = @FromDepartmentID

--Search Transfer By FromProjectID
go
create proc spSearchTransferByFromProjectID
	@FromProjectID nvarchar(10)
as
select * from Transfer where FromProjectID = @FromProjectID

--Search Transfer By ToProjectID
go
create proc spSearchTransferByToProjectID
	@ToProjectID nvarchar(10)
as
select * from Transfer where ToProjectID = @ToProjectID

--Search Transfer By ToLocationID
go
create proc spSearchTransferByToLocationID
	@ToLocationID nvarchar(10)
as
select * from Transfer where ToLocationID = @ToLocationID

--Search Transfer By From DepartmentID
go
create proc spSearchTransferByToDepartmentID
	@ToDepartmentID nvarchar(10)
as
select * from Transfer where ToDepartmentID = @ToDepartmentID

--------------------------------------------------------------------------------------------------------------------------------
--PROC TOTAL TRANSFER
--Count Total Transfer
go
create proc spTotalTransfer
as
select * from Transfer

--Count Total Approve
go
create proc spTotalStatus
as
select * from Transfer where Status=1

--Count Total Not Approve
go
create proc spTotalNotStatus
as
select * from Transfer where Status=0